package com.hd.core.project.module.utils;

import com.alibaba.fastjson.JSONObject;

import com.hd.core.common.utils.file.FileUtils;
import com.hd.core.common.utils.security.ShiroUtils;
import com.hd.core.framework.shiro.web.session.OnlineWebSessionManager;
import com.hd.core.project.module.config.BaseModuleProperties;
import com.hd.core.project.module.domain.EduUploadData;
import com.hd.core.project.module.domain.EduUploadDataTeacher;
import com.hd.core.project.module.domain.EduUploadDataTitle;
import com.hd.core.project.system.user.domain.User;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * 文件资料工具-教务科模块上传上来的Excel文件
 */
@SuppressWarnings("all")
public class FileDataUtils {

    private static final JSONObject jsonObject = new JSONObject();

    private static final String SPLIT_CHARACTER_COMMA = ",";

    private static final String SPLIT_CHARACTER_POINT = ".";

    private static final String SPLIT_CHARACTER_SLANT_LINE = "/";

    private static final String SPLIT_CHARACTER_HORIZONTAL = "-";

    private static final Logger log = LoggerFactory.getLogger(OnlineWebSessionManager.class);

    /**
     * 拆分日期,只支持此类格式
     * 2018-01 / 2018,01 / 2018.01 / 2018/01
     * @return year:年份 month:月份
     */
    public static Map<String,String> splitDate(String date){
        String[] split = null;
        Map<String,String> result = new HashMap<String,String>();
        if(date.contains(SPLIT_CHARACTER_HORIZONTAL)){
            split = date.split(SPLIT_CHARACTER_HORIZONTAL);
        }else if (date.contains(SPLIT_CHARACTER_COMMA)){
            split = date.split(SPLIT_CHARACTER_COMMA);
        }else if (date.contains(SPLIT_CHARACTER_POINT)){
            split = date.split("\\"+SPLIT_CHARACTER_POINT);
        }else if (date.contains(SPLIT_CHARACTER_SLANT_LINE)){
            split = date.split("\\"+SPLIT_CHARACTER_SLANT_LINE);
        }
        result.put("year",split[0]);
        result.put("month",split[1]);
        return result;
    }

    /**
     * 切割日期和文件名两部分
     * 目前只支持 "日期月文件名" 的格式
     * TODO 后期可以对方法进行增强
     */
    public static Map<String,String> splitDateName(String fileName){
        Map<String,String> result = null;
        //去掉文件名后缀
        String fName = FileUtils.cleanFileNameSuffix(fileName);
        //将日期部分和文件名部分分开
        String[] splitFName = fName.split("月");
        if(splitFName.length > 0){
            result = new HashMap<String,String>();
            //返回数据
            result.put("date",splitFName[0]);
            result.put("name",splitFName[1]);
        }
        return result;
    }

    /**
     * 根据资料文件名,返回资料存储的对应目录
     * @param fileName 文件名,可以带后缀会自动处理
     */
    public static String getFileDataPath(String fileName){
        Map<String, String> splitResult = splitDateName(fileName);
        if(splitResult != null){
            //将日期部分的 "."替换成路径的 "/"
           String fileNameDatePath =
                   splitResult.get("date").replace(SPLIT_CHARACTER_POINT,SPLIT_CHARACTER_SLANT_LINE);
           return SPLIT_CHARACTER_SLANT_LINE +
                   splitResult.get("name") + SPLIT_CHARACTER_SLANT_LINE +
                   fileNameDatePath + SPLIT_CHARACTER_SLANT_LINE;
        }
        return null;
    }

    /**
     * 获取文件类型
     * 0:以下都不是
     * 1:加课汇总 2:课程课时统计 3:课程代课统计
     * 4:课头补贴 5:晚自习课时汇总 6:晚自习代课汇总
     * @param fileName 文件名称部分,注意不是文件名,是文件名中的名称部分
     * @return
     */
    public static Integer getFileType(String fileName,String sheetName){
        switch (fileName){
            case "加课汇总":
                return 1;
            case "课程课时统计":
                return sheetName.equals("课程课时统计") ? 2 : 3;
            case "课头补贴":
                return 4;
            case "晚自习课时统计":
                return sheetName.equals("晚自习课时汇总") || sheetName.equals("晚自习课时") ? 5 : 6;
        }
        return 0;
    }

    /**
     * 返回所有Excel文件解析出来的所有Sheet页对象
     */
    public static Map<Sheet,File> getFileSheetParser(List<File> files) throws IOException, InvalidFormatException {
        int sheetNumber;
        Workbook workbook = null;
        Map<Sheet, File> allSheet = new LinkedHashMap<Sheet, File>();
        for(File file : files){
            workbook = ExcelUtilsWrapper.getWorkbook(file);
            //获取到这个Excel文件的Sheet数量
            sheetNumber = workbook.getNumberOfSheets();
            //循环拿到sheet页
            for(int i = 0; i < sheetNumber && i < 2; i++){
                allSheet.put(workbook.getSheetAt(i),file);
            }
        }
        return allSheet;
    }

    /**
     * 将Sheet页对象解析成 ↓
     * @see EduUploadDataTitle
     * @see EduUploadDataTeacher
     */
    public static Map<String,List> parseEduUploadData(Map<Sheet,File> sheetAndFileMap){
        Map<String,List> result = new LinkedHashMap<String,List>();
        List<EduUploadDataTitle> titleResult = new ArrayList<EduUploadDataTitle>();
        //全部sheet页的teacher数据
        List<List<EduUploadDataTeacher>> allSheetTeachers = new ArrayList<List<EduUploadDataTeacher>>();
        for(Sheet sheet : sheetAndFileMap.keySet()){
            EduUploadDataTitle eduUploadDataTitle = parseExcelTitle(sheet, sheetAndFileMap);
            List<EduUploadDataTeacher> eduUploadDataTeachers = parseExcelData(sheet, eduUploadDataTitle);
            titleResult.add(eduUploadDataTitle);
            allSheetTeachers.add(eduUploadDataTeachers);
        }
        result.put("eduTitle",titleResult);
        result.put("eduTeacher",allSheetTeachers);
        return result;
    }

    /**
     * 解析Excel文件标题
     * @param sheet 需要解析的sheet页对象
     * @param sheetAndFileMap
     */
    public static EduUploadDataTitle parseExcelTitle(Sheet sheet,Map<Sheet,File> sheetAndFileMap){
        //存储文件名中的日期部分
        Map<String, String> fileDateMap = null;
        //存储文件名中的名称部分
        Map<String, String> fileDateNameMap = null;
        //对应的标题数据
        List<LinkedHashMap<String, String>> titleJsonMap = null;
        //存储标题实体对象 一个Sheet页对应一个EduUploadDataTitle
        EduUploadDataTitle title = new EduUploadDataTitle();
        titleJsonMap = parseExcelTitleToList(sheet, title, null);
        fileDateNameMap = splitDateName(sheetAndFileMap.get(sheet).getName());
        fileDateMap = splitDate(fileDateNameMap.get("date"));
        setEduUploadDataTitleValue(
            title,
            sheetAndFileMap.get(sheet),
            sheet.getSheetName(),
            Integer.parseInt(fileDateMap.get("year")),
            Integer.parseInt(fileDateMap.get("month")),
            fileDateNameMap.get("name"),
            JSONObject.toJSONString(titleJsonMap)
        );
        return title;
    }

    /**
     * 解析标题行下的教师数据
     * @param sheet Sheet页对象
     * @param title 当前sheet页的标题对象 {@link EduUploadDataTitle}
     */
    public static List<EduUploadDataTeacher> parseExcelData(Sheet sheet, EduUploadDataTitle title){
        //存储教师数据
        int titleIndexY = Integer.parseInt(title.getIndex());
        //标题行cell的数量
        int titleCellNum = sheet.getRow(titleIndexY).getPhysicalNumberOfCells();
        List<LinkedHashMap<String,String>> saveTeacherData = null;
        List<EduUploadDataTeacher> teachers = new ArrayList<EduUploadDataTeacher>();
        for(int i = titleIndexY  + 1; i < sheet.getPhysicalNumberOfRows(); i++){
            Row currentRow = sheet.getRow(i);
            Cell currentRowOfFirstCell = currentRow.getCell(0);
            //存储每个单元格的值和批注
            LinkedHashMap<String,String> valueAndTopicMap = null;
            //一个教师对应一个,存储单元格的值和批注
            saveTeacherData = new ArrayList<LinkedHashMap<String,String>>();
            try {
                //这一行是将标题行的下一行的第一个单元格转换成int类型能转换的话就代表是老师行数据
                //转换失败抛出异常,执行下一次循环
                Integer.parseInt(ExcelUtilsWrapper.getCellValue(currentRowOfFirstCell, true));
                //添加单元格标题和教师名称到list中
                String teacherName = addTeacherIdAndName(currentRow,saveTeacherData);
                //转换成功,上面的语句就不会报错就可以继续执行下面的语句
                for(int n = 2; n < titleCellNum; n++){
                    Cell currentCell = currentRow.getCell(n);
                    String currentCellTopic = ExcelUtilsWrapper.getTopic(currentCell);
                    String currentCellValue = ExcelUtilsWrapper.getCellValue(currentCell, true);
                    valueAndTopicMap = new LinkedHashMap<String,String>();
                    valueAndTopicMap.put(currentCellValue,currentCellTopic);
                    saveTeacherData.add(valueAndTopicMap);
                }
                //创建教师数据存储实体
                teachers.add(new EduUploadDataTeacher(
                        teacherName, //TODO 工具类本身不应该依赖其他工具类,这里以后想办法处理
                        ShiroUtils.getUserId(),
                        title.getYear(),
                        title.getMonth(),
                        title.getType(),
                        jsonObject.toJSONString(saveTeacherData)
                ));
            }catch (Exception e){
                System.out.println(e.getMessage());
                break;
            }
        }
        return teachers;
    }

    /**
     * 设置 {@link EduUploadDataTitle} 的参数
     * @param title {@link EduUploadDataTitle} 对象 这里一定要使用外部传进来的
     * @param file 当前sheet对应的file对象
     * @param sheetName sheet页名称
     * @param year 当前资料文件对应的年份
     * @param month 当前资料文件对应的月份
     * @param fileDateName 当前文件资料的文件名称
     * @param titleJson 标题json数据
     */
    private static EduUploadDataTitle setEduUploadDataTitleValue(
            EduUploadDataTitle title,
            File file,String sheetName,Integer year,
            Integer month,String fileDateName,String titleJson){
        title.setYear(year);
        title.setMonth(month);
        title.setFilePath(file.getPath());
        title.setSheetName(sheetName);
        title.setTitleJson(titleJson);
        title.setType(getFileType(fileDateName,sheetName));
        return title;
    }


    /**
     * 解析文件资料
     * (分析出资料文件中的标题行,以及各个老师对应的索引数据)
     */
    public static Map<String,List> parseFileData(List<File> files){
        try {
            //将所有的Excel文件解析成Sheet对象
            Map<Sheet, File> fileSheetParserResult = getFileSheetParser(files);
            return parseEduUploadData(fileSheetParserResult);
        } catch (Exception e) {
            log.error("Excel文件解析失败{}",e.getMessage());
            return null;
        }
    }

    /**
     * 解析Excel文件中的标题行,并将数据打包成map集合,注意这里必须是有序集合
     * Key为单元格内的值,Value为单元格的批注的值
     * @param sheet excel文件对应的sheet页对象
     * @param title 存储title数据的对象实体
     * @param titleCellValue 标题行第一个单元格的值 不填默认值为:"序号"
     */
    public static List<LinkedHashMap<String,String>> parseExcelTitleToList(Sheet sheet, EduUploadDataTitle title, String titleCellValue){
        //记录标题行的Cell数量,因为教师数据行读到的数值不准确
        int physicalNumberOfCells;
        LinkedHashMap<String,String> valueAndTopicMap = null;
        titleCellValue = null != titleCellValue ? titleCellValue : "序号";
        List<LinkedHashMap<String,String>> result = new ArrayList<LinkedHashMap<String,String>>();
        for(int n = 0; n < sheet.getPhysicalNumberOfRows(); n++){
            Row currentRow = sheet.getRow(n);
            Cell currentRowOfFirstCell = currentRow.getCell(0);
            String cellValue = ExcelUtilsWrapper.getCellValue(currentRowOfFirstCell, true);
            if(cellValue.equals(titleCellValue)){
                title.setIndex(String.valueOf(currentRow.getRowNum()));
                physicalNumberOfCells = currentRow.getPhysicalNumberOfCells();
                for (int cellIndex = 0; cellIndex < physicalNumberOfCells; cellIndex++){
                    Cell currentIndexOfCell = currentRow.getCell(cellIndex);
                    String value = ExcelUtilsWrapper.getCellValue(currentIndexOfCell, true);
                    Comment topic = currentIndexOfCell.getCellComment();
                    valueAndTopicMap = new LinkedHashMap<String,String>();
                    valueAndTopicMap.put(value,topic==null?"":topic.getString().getString());
                    result.add(valueAndTopicMap);
                }
                break;
            }
        }
        return result;
    }

    /**
     * 存储教师id和教师名称两个单元格
     * @param currentRow 当前教师的数据行
     * @param saveTeacherData 用来存储当前教师每个单元格数据的ArrayList集合
     * @return 教师名称
     */
    public static String addTeacherIdAndName(Row currentRow,List<LinkedHashMap<String,String>> saveTeacherData){
        //存储每个单元格的值和批注
        LinkedHashMap<String,String> valueAndTopicMap = null;
        //id单元格Cell
        Cell currentRowOfFirstCell = currentRow.getCell(0);
        String teacherCellId = ExcelUtilsWrapper.getCellValue(currentRowOfFirstCell,true);
        String teacherCellIdTopic = ExcelUtilsWrapper.getTopic(currentRowOfFirstCell);
        //获取教师名称和批注 以及 序号也添加进去
        Cell teacherNameCell = currentRow.getCell(1);
        String teacherName = ExcelUtilsWrapper.getCellValue(teacherNameCell,true);
        String teacherNameTopic = ExcelUtilsWrapper.getTopic(teacherNameCell);
        //先将教师名称添加进去 这里注意顺序 先添加id在添加教师名称
        valueAndTopicMap = new LinkedHashMap();
        valueAndTopicMap.put(teacherCellId,teacherCellIdTopic);
        saveTeacherData.add(valueAndTopicMap);
        valueAndTopicMap = new LinkedHashMap();
        valueAndTopicMap.put(teacherName,teacherNameTopic);
        saveTeacherData.add(valueAndTopicMap);
        return teacherName;
    }

    /*public static List<LinkedHashMap<String,String>> parseExcelTitle(Sheet sheet, EduUploadDataTitle title, String titleCellValue){
        LinkedHashMap<String,String> valueAndTopicMap = null;
        titleCellValue = (null != titleCellValue ? titleCellValue : "序号");
        List<LinkedHashMap<String,String>> result = new ArrayList<LinkedHashMap<String,String>>();
        for(int n = 0; n < sheet.getPhysicalNumberOfRows(); n++){
            Row currentRow = sheet.getRow(n);
            Cell currentRowOfFirstCell = currentRow.getCell(0);
            String cellValue = ExcelUtilsWarpper.getCellValue(currentRowOfFirstCell, true);
            if(cellValue.equals(titleCellValue)){
                title.setIndex(String.valueOf(currentRow.getRowNum()));
                for (int cellIndex = 0; cellIndex < currentRow.getPhysicalNumberOfCells(); cellIndex++){
                    Cell currentIndexOfCell = currentRow.getCell(cellIndex);
                    String value = ExcelUtilsWarpper.getCellValue(currentIndexOfCell, true);
                    Comment topic = currentIndexOfCell.getCellComment();
                    *//*valueAndTopicMap.put(value,topic==null?"":topic.getString().getString());*//*
                    valueAndTopicMap = new LinkedHashMap<String,String>();
                    valueAndTopicMap.put(value,topic==null?"":topic.getString().getString());
                    result.add(valueAndTopicMap);
                }
                break;
            }
        }
        return result;
    }*/

    /**
     * 将前台传过来的excel 文件写入到硬盘中
     * 该方法配合EduUploadData使用并不通用
     * @param files file对象
     * @param properties 对应的配置类对象
     * @param user 当前用户对象
     * @return
     * @throws IOException
     */
    public static Map<String,List> transferToDiskAll(MultipartFile[] files, BaseModuleProperties properties, User user) throws IOException {
        //excel拼接出来的目录路径,不包含文件名
        String transferToPath = null;
        //完整路径,带文件名
        String fileCompletePath = null;
        //存储保存到硬盘的文件对象
        List<File> diskFileData = new ArrayList<File>();
        //返回需要的数据
        Map<String,List> result = new HashMap<String,List>();
        //存储上传的所有的资料文件完整的路径
        Set<String> filesCompletePath = new LinkedHashSet<String>();
        //存储上传的文件记录
        List<EduUploadData> eduUploadDatas = new ArrayList<EduUploadData>();
        for(MultipartFile file : files){
            transferToPath = properties.getFileDataSaveDiskPath() + FileDataUtils.getFileDataPath(file.getOriginalFilename());
            fileCompletePath = transferToPath + file.getOriginalFilename();
            File transferToFile = new File(transferToPath);
            if(!transferToFile.exists()){
                transferToFile.mkdirs();
            }
            eduUploadDatas.add(new EduUploadData(
                    user.getUserName(),
                    new Date(),
                    new Date(),
                    1,
                    file.getOriginalFilename(),
                    fileCompletePath
            ));
            diskFileData.add(new File(fileCompletePath));
            file.transferTo(diskFileData.get(diskFileData.size() - 1));
            filesCompletePath.add(fileCompletePath);
        }
        result.put("diskFileData",diskFileData);
        result.put("eduUploadDatas",eduUploadDatas);
        return result;
    }
}
